from flask import Flask, render_template, request, redirect, url_for, flash
from models import db, User
from sqlalchemy import or_, text, desc, asc
from math import ceil

app = Flask(__name__)

# 添加max和min函数到Jinja2全局变量
app.jinja_env.globals.update(max=max, min=min)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 设置每页显示的记录数
USERS_PER_PAGE = 10

# 初始化数据库
db.init_app(app)

# 创建应用上下文并创建数据库表
with app.app_context():
    db.create_all()

def reset_id_sequence():
    """重置用户ID序列，使其连续"""
    try:
        # 获取所有用户并按ID排序
        users = User.query.order_by(User.id).all()
        
        # 重新设置ID
        for index, user in enumerate(users, start=1):
            user.id = index
        
        # 提交更改
        db.session.commit()
        
        # 重置SQLite的自增序列
        with db.engine.connect() as conn:
            conn.execute(text("DELETE FROM sqlite_sequence WHERE name='user'"))
            conn.execute(text(f"INSERT INTO sqlite_sequence (name, seq) VALUES ('user', {len(users)})"))
            
    except Exception as e:
        db.session.rollback()
        print(f"重置ID序列时发生错误: {str(e)}")

@app.route('/')
def index():
    # 获取页码、搜索参数和排序参数
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    sort_by = request.args.get('sort', 'id')  # 默认按ID排序
    order = request.args.get('order', 'asc')  # 默认升序

    # 构建查询
    query = User.query

    # 如果有搜索条件，添加过滤
    if search:
        query = query.filter(
            or_(
                User.username.ilike(f'%{search}%'),
                User.email.ilike(f'%{search}%')
            )
        )

    # 添加排序
    if sort_by == 'username':
        query = query.order_by(asc(User.username) if order == 'asc' else desc(User.username))
    elif sort_by == 'email':
        query = query.order_by(asc(User.email) if order == 'asc' else desc(User.email))
    else:  # 默认按ID排序
        query = query.order_by(asc(User.id) if order == 'asc' else desc(User.id))

    # 获取总记录数和总页数
    total_users = query.count()
    total_pages = ceil(total_users / USERS_PER_PAGE)

    # 获取当前页的用户数据
    users = query.offset((page - 1) * USERS_PER_PAGE)\
                .limit(USERS_PER_PAGE)\
                .all()

    return render_template(
        'index.html',
        users=users,
        page=page,
        total_pages=total_pages,
        has_prev=page > 1,
        has_next=page < total_pages,
        sort_by=sort_by,
        order=order,
        total_users=total_users
    )

@app.route('/add', methods=['GET', 'POST'])
def add_user():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        
        # 检查用户名和邮箱是否已存在
        existing_user = User.query.filter(
            or_(User.username == username, User.email == email)
        ).first()
        
        if existing_user:
            flash('用户名或邮箱已存在!', 'danger')
            return render_template('add_user.html', username=username, email=email)
        
        try:
            # 创建新用户
            new_user = User(username=username, email=email)
            db.session.add(new_user)
            db.session.commit()
            flash('用户添加成功!', 'success')
            return redirect(url_for('index'))
        except Exception as e:
            db.session.rollback()
            flash(f'添加用户时发生错误：{str(e)}', 'danger')
            return render_template('add_user.html', username=username, email=email)
    
    return render_template('add_user.html')

@app.route('/edit/<int:user_id>', methods=['GET', 'POST'])
def edit_user(user_id):
    user = User.query.get_or_404(user_id)
    
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        
        # 检查用户名和邮箱是否与其他用户冲突
        existing_user = User.query.filter(
            or_(User.username == username, User.email == email),
            User.id != user_id
        ).first()
        
        if existing_user:
            flash('用户名或邮箱已被其他用户使用!', 'danger')
            return render_template('edit_user.html', user=user)
        
        try:
            user.username = username
            user.email = email
            db.session.commit()
            flash('用户信息更新成功!', 'success')
            return redirect(url_for('index'))
        except Exception as e:
            db.session.rollback()
            flash(f'更新用户时发生错误：{str(e)}', 'danger')
            return render_template('edit_user.html', user=user)
    
    return render_template('edit_user.html', user=user)

@app.route('/delete/<int:user_id>')
def delete_user(user_id):
    user = User.query.get_or_404(user_id)
    try:
        db.session.delete(user)
        db.session.commit()
        
        # 删除用户后重置ID序列
        reset_id_sequence()
        
        flash('用户删除成功!', 'success')
    except Exception as e:
        db.session.rollback()
        flash(f'删除用户时发生错误：{str(e)}', 'danger')
    
    return redirect(url_for('index'))

if __name__ == '__main__':
    app.run(debug=True)